USE [DB_BANK_HOUR]
GO

/****** Object:  UserDefinedFunction [dbo].[funcDiffHour]    Script Date: 04/23/2014 09:15:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[funcDiffHour]
(
	@entr time, @extBreak time, @entrBreak time, @exit time
)
RETURNS varchar(5)
AS
BEGIN
	DECLARE @result varchar(5)
	DECLARE @value1 int
	DECLARE @value2 int
	DECLARE @value3 int
	
	
	Set @value1 = ISNULL(DATEDIFF(Mi,@entr,@exit),0)
	Set @value2 = ISNULL(DATEDIFF(Mi,@extBreak,@entrBreak),0)
	SET @value3 = @value1 - @value2

	IF  @value1 = 0 or @value2 = 0
	BEGIN
		set @result = ''
	END
	ELSE
	BEGIN
		set @result = (SELECT RIGHT('0' + CAST((@value3 / 60) As VARCHAR(2)),2) + ':' + RIGHT('0' + CAST((@value3 % 60) As VARCHAR(2)),2)) 
	END
		
	RETURN @result

END

GO


